import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import os
import json
import re
import sqlparse
from sqlparse.sql import IdentifierList, Identifier
from sqlparse.tokens import Keyword

class SQLParser:
    """封装SQL解析功能"""
    @staticmethod
    def parse_table_name(parsed):
        """从SQL解析结果中提取表名"""
        for token in parsed.tokens:
            if token.ttype is Keyword and token.value.upper() == 'FROM':
                # 跳过FROM关键字，获取下一个标识符作为表名
                next_idx = parsed.token_index(token) + 1
                if next_idx < len(parsed.tokens):
                    table_token = parsed.tokens[next_idx]
                    if isinstance(table_token, (IdentifierList, Identifier)):
                        if isinstance(table_token, Identifier):
                            return table_token.get_real_name()
                        elif table_token.get_identifiers():
                            return table_token.get_identifiers()[0].get_real_name()
        return None

    @staticmethod
    def parse_columns(sql):
        """从SQL语句中提取列名（处理复杂表达式）并转换为小写"""
        # 提取SELECT和FROM之间的列信息（处理复杂表达式和多行）
        column_pattern = re.compile(r'SELECT\s+(.*?)\s+FROM', re.IGNORECASE | re.DOTALL)
        column_match = column_pattern.search(sql)
        if not column_match:
            return []
        columns = []
        # 处理复杂表达式中的逗号分割问题
        raw_text = column_match.group(1)
        # 更智能的分割方法：基于非括号内逗号的分割
        current = ""
        bracket_count = 0
        raw_columns = []
        for char in raw_text:
            if char == '(':
                bracket_count += 1
            elif char == ')':
                bracket_count -= 1
            elif char == ',' and bracket_count == 0:
                raw_columns.append(current.strip())
                current = ""
                continue
            current += char
        if current.strip():
            raw_columns.append(current.strip())
        for col in raw_columns:
            # 尝试匹配显式别名
            alias_match = re.search(
                r'''\bAS\s+["']?(\w+)["']?$''',
                col,
                re.IGNORECASE
            )
            if alias_match:
                columns.append(alias_match.group(1).lower())
                continue
            # 尝试匹配隐式别名（SQL允许省略AS）
            implicit_alias_match = re.search(
                r'''\s["']?(\w+)["']?$''',
                col,
                re.IGNORECASE
            )
            if implicit_alias_match:
                columns.append(implicit_alias_match.group(1).lower())
                continue
            # 作为列表达式的一部分，取最后有效的单词
            last_word_match = re.search(
                r'\b(\w+)\s*$',
                col,
                re.IGNORECASE
            )
            if last_word_match:
                columns.append(last_word_match.group(1).lower())
            else:
                columns.append(col.strip().lower())
        return columns

    @classmethod
    def extract_sql_components(cls, sql):
        """提取SQL语句中的表名和列信息"""
        try:
            parsed = sqlparse.parse(sql)[0]
            table_name = cls.parse_table_name(parsed)
            columns = cls.parse_columns(sql)
            return table_name, columns
        except Exception as e:
            print(f"SQL解析错误: {e}")
            return None, []
class DataxJsonGenerator:
    """DataX JSON生成器应用"""
    def __init__(self):
        self.root = tk.Tk()
        self.root.title("DataX JSON生成器")
        self.root.geometry("1200x900")
        self.root.columnconfigure(0, weight=1)
        self.root.rowconfigure(1, weight=1)
        # 初始化UI
        self._setup_ui()
        # 示例值
        self.sql_text.insert('1.0', self.get_sql_example())
        self.presql_text.insert('1.0', self.get_presql_example())
        self.target_text.insert('1.0', self.get_target_example())
        # 资源目录
        self.json_dir = "dataxjson/"
        self.conn_dir = "connectdata/"
        # 加载下拉菜单选项
        self._load_combo_options()
    def get_sql_example(self):
        """返回SQL示例"""
        return """-- 示例：
SELECT 
    user_id AS id,
    user_name AS name,
    DATE(register_time) AS reg_date
FROM marketing.user_data
WHERE reg_date = CURRENT_DATE();
"""

    def get_presql_example(self):
        """返回前置SQL示例"""
        return "TRUNCATE TABLE demo.user_data;"
    def get_target_example(self):
        """返回目标表示例"""
        return "demo.user_summary"
    def _load_combo_options(self):
        """加载下拉菜单选项"""
        # 加载JSON模板
        json_files = [f for f in os.listdir(self.json_dir) if f.endswith('.json')]
        self.json_combo['values'] = json_files
        if json_files:
            self.json_combo.current(0)
        # 加载连接配置
        conn_files = [f for f in os.listdir(self.conn_dir) if f.endswith('.json')]
        self.source_combo['values'] = conn_files
        self.target_combo['values'] = conn_files
        if conn_files:
            self.source_combo.current(0)
            self.target_combo.current(0)
    def _setup_ui(self):
        """设置UI布局"""
        # 顶部配置区域
        config_frame = ttk.LabelFrame(self.root, text="配置区域", padding=10)
        config_frame.grid(row=0, column=0, padx=10, pady=5, sticky="ew")
        config_frame.columnconfigure(0, weight=1)
        config_frame.columnconfigure(1, weight=1)
        config_frame.columnconfigure(2, weight=1)
        # JSON模板选择
        json_frame = ttk.Frame(config_frame)
        json_frame.grid(row=0, column=0, padx=5, pady=5, sticky="nsew")
        ttk.Label(json_frame, text='JSON模板').pack(anchor="w")
        self.json_combo = ttk.Combobox(json_frame, state="readonly")
        self.json_combo.pack(fill="x", pady=2)
        # 源数据库选择
        source_frame = ttk.Frame(config_frame)
        source_frame.grid(row=0, column=1, padx=5, pady=5, sticky="nsew")
        ttk.Label(source_frame, text='源数据库').pack(anchor="w")
        self.source_combo = ttk.Combobox(source_frame, state="readonly")
        self.source_combo.pack(fill="x", pady=2)
        # 目标数据库选择
        target_frame = ttk.Frame(config_frame)
        target_frame.grid(row=0, column=2, padx=5, pady=5, sticky="nsew")
        ttk.Label(target_frame, text='目标数据库').pack(anchor="w")
        self.target_combo = ttk.Combobox(target_frame, state="readonly")
        self.target_combo.pack(fill="x", pady=2)
        # 中间编辑区域
        edit_frame = ttk.LabelFrame(self.root, text="输入区域", padding=10)
        edit_frame.grid(row=1, column=0, padx=10, pady=5, sticky="nsew")
        edit_frame.columnconfigure(0, weight=1)
        edit_frame.columnconfigure(1, weight=2)
        edit_frame.rowconfigure(0, weight=1)
        # SQL输入区
        sql_panel = ttk.Frame(edit_frame)
        sql_panel.grid(row=0, column=0, padx=5, pady=5, sticky="nsew")
        sql_panel.rowconfigure(1, weight=1)

        ttk.Label(sql_panel, text='SQL查询语句:').grid(row=0, column=0, sticky="w")
        self.sql_text = tk.Text(sql_panel, width=50, height=10)
        self.sql_text.grid(row=1, column=0, sticky="nsew", pady=5)

        ttk.Label(sql_panel, text='前置SQL:').grid(row=2, column=0, sticky="w", pady=(10, 0))
        self.presql_text = tk.Text(sql_panel, width=50, height=3)
        self.presql_text.grid(row=3, column=0, sticky="ew", pady=5)

        ttk.Label(sql_panel, text='目标表名:').grid(row=4, column=0, sticky="w", pady=(10, 0))
        self.target_text = tk.Text(sql_panel, width=50, height=1)
        self.target_text.grid(row=5, column=0, sticky="ew", pady=5)

        # 结果预览区
        result_panel = ttk.Frame(edit_frame)
        result_panel.grid(row=0, column=1, padx=5, pady=5, sticky="nsew")
        result_panel.rowconfigure(1, weight=1)

        ttk.Label(result_panel, text='生成结果预览:').grid(row=0, column=0, sticky="w")
        self.result_text = tk.Text(result_panel, width=80)
        self.result_text.grid(row=1, column=0, sticky="nsew", pady=5)

        # 底部按钮区域
        button_frame = ttk.Frame(self.root)
        button_frame.grid(row=2, column=0, padx=10, pady=10, sticky="e")

        self.confirm_btn = ttk.Button(
            button_frame,
            text="生成 DataX JSON",
            command=self.generate_json,
            width=20
        )
        self.confirm_btn.pack(side="right", padx=5)

    @staticmethod
    def clean_sql_text(sql_text):
        """清理SQL文本中的注释和多余空格"""
        # 移除行末注释
        cleaned_lines = []
        for line in sql_text.split('\n'):
            comment_idx = line.find('--')
            if comment_idx >= 0:
                line = line[:comment_idx]
            cleaned_lines.append(line.strip())

        # 合并为单行并清理多余空格
        cleaned_sql = ' '.join(cleaned_lines)
        cleaned_sql = re.sub(r"\s+", " ", cleaned_sql)
        return cleaned_sql.strip()

    @staticmethod
    def clean_target_text(target_text):
        """清理目标表名文本"""
        target_text = re.sub(r"[\t\n`\"]", "", target_text)
        return target_text.strip()

    def load_connection_info(self, file_path):
        """加载连接配置文件"""
        try:
            with open(file_path, 'r', encoding='utf-8') as f:
                conn_data = json.load(f)

            # 返回需要的字段，支持大小写兼容
            keys = ['user', 'password', 'address', 'database', 'jdbcUrl',
                    'driverClassName', 'loadUrl']
            return {
                key: conn_data.get(key) or conn_data.get(key.capitalize()) or ''
                for key in keys
            }
        except Exception as e:
            messagebox.showerror("加载错误", f"加载连接配置文件出错: {str(e)}")
            return None

    def generate_json(self):
        """生成DataX JSON配置"""
        try:
            # 获取用户输入
            json_template = os.path.join(self.json_dir, self.json_combo.get())
            source_config = os.path.join(self.conn_dir, self.source_combo.get())
            target_config = os.path.join(self.conn_dir, self.target_combo.get())
            raw_sql = self.sql_text.get("1.0", tk.END)
            presql = self.presql_text.get("1.0", tk.END)
            target_table = self.target_text.get("1.0", tk.END)

            # 清理输入
            cleaned_sql = self.clean_sql_text(raw_sql)
            target_table = self.clean_target_text(target_table)

            # 解析SQL
            parsed_table, parsed_columns = SQLParser.extract_sql_components(cleaned_sql)
            if not parsed_columns:
                messagebox.showwarning("SQL解析", "未能提取到有效的列名")
                return

            # 加载连接配置
            source_conn = self.load_connection_info(source_config)
            target_conn = self.load_connection_info(target_config)
            if not source_conn or not target_conn:
                return

            # 处理目标表
            if '.' in target_table:
                db_name, tbl_name = target_table.split('.', 1)
            else:
                db_name = target_conn.get('database', '') or target_conn.get('selectedDatabase', '')
                tbl_name = target_table

            # 加载JSON模板
            with open(json_template, 'r', encoding='utf-8') as f:
                json_config = json.load(f)

            # 更新reader配置
            reader = json_config['job']['content'][0]['reader']
            if 'parameter' in reader:
                reader_param = reader['parameter']

                # 更新连接信息
                if 'connection' in reader_param:
                    for conn in reader_param['connection']:
                        if 'querySql' in conn:
                            conn['querySql'] = [cleaned_sql]
                        if 'jdbcUrl' in conn:
                            conn['jdbcUrl'] = [source_conn.get('jdbcUrl', '')]

                # 更新认证信息
                reader_param['username'] = source_conn.get('user', '')
                reader_param['password'] = source_conn.get('password', '')

            # 更新writer配置
            writer = json_config['job']['content'][0]['writer']
            if 'parameter' in writer:
                writer_param = writer['parameter']

                # 更新列信息
                if 'column' in writer_param:
                    writer_param['column'] = parsed_columns

                # 更新前置SQL
                if 'preSql' in writer_param:
                    writer_param['preSql'] = [presql]

                # 更新连接信息
                if 'connection' in writer_param:
                    for conn in writer_param['connection']:
                        if 'table' in conn:
                            if 'doris' in writer['name']:
                                conn['table'] = [tbl_name]
                            else:
                                conn['table'] = [db_name+'.'+tbl_name]
                        if 'selectedDatabase' in conn:
                            conn['selectedDatabase'] = db_name
                        if 'jdbcUrl' in conn:
                            conn['jdbcUrl'] = target_conn.get('jdbcUrl', '')

                # 更新其他连接信息
                if 'loadUrl' in writer_param:
                    writer_param['loadUrl'] = [target_conn.get('loadUrl', '')]

                writer_param['username'] = target_conn.get('user', '')
                writer_param['password'] = target_conn.get('password', '')

            # 显示结果
            formatted_json = json.dumps(json_config, indent=4, ensure_ascii=False)
            self.result_text.delete(1.0, tk.END)
            self.result_text.insert(tk.END, formatted_json)

        except Exception as e:
            messagebox.showerror("生成错误", f"生成JSON时发生错误:\n{str(e)}")

    def run(self):
        """运行应用程序"""
        self.root.mainloop()


if __name__ == "__main__":
    app = DataxJsonGenerator()
    app.run()